package test.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.mysql.jdbc.ResultSetMetaData;


//import test.com.aspire.community.config.Config;

public class Mysql {
	private static String host;
	private static String dbname;
	private static String username;
	private static String pwd;
	private static Connection conn;
	
	private static Logger logger = Logger.getLogger(Mysql.class);
	
	public static boolean connect(String thisHost, String thisDbname, String username, String pwd) {
		if (conn != null && host.equals(thisHost) && dbname.equals(thisDbname)) {
			return true;
		}
		close();
	    setHost(thisHost);
	    setDbname(thisDbname);
	    setUsername(username);
	    setPwd(pwd);
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = java.sql.DriverManager.getConnection(  
					        "jdbc:mysql://" + host + "/" + dbname + "?useUnicode=true&characterEncoding=UTF-8",  
					        username, pwd);
		} catch (Exception e) {
			logger.info("HOST:"+host+",mysql数据库连接失败");
			e.printStackTrace();
		}
		logger.info("HOST:"+host+",mysql数据库连接成功");
		return true;
	}
	
	public static void close() {
		if (conn == null) {
			return;
		}
		try {
			conn.close();
			logger.info("mysql关闭连接");
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}
	
	//执行sql语句
	public static JsonResult excute(String sqlstring) {
		try {
			mysqlConnect("admin");
			JsonResult result = new JsonResult();
			if (!sqlstring.trim().isEmpty()) {
        		Statement stmt = conn.createStatement();
        		stmt.executeUpdate(sqlstring);
        		stmt.close();
			}
	    	return result;
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(sqlstring);
			JSONObject object = new JSONObject();
			object.put("msg", "操作失败");
			object.put("code", 1);
			return JsonResult.parse(object.toString());
		}
	}
	
	private static void mysqlConnect(String source) {
		GetProperties properties = GetProperties.loadPrperties("jdbc.properties");
		String conn = properties.getString(source);
		String [] conns = conn.split("\\|");
		Mysql.connect(conns[0], conns[3], conns[1], conns[2]);
	}
	
	//查询sql
	public static JsonResult select(String sqlsting) {
		try {
			mysqlConnect("admin");
			Statement stmt = conn.createStatement();
			ResultSet res;
			JSONArray arr = null;
//			logger.info("SQL查询："+sqlsting);
			res = stmt.executeQuery(sqlsting);
			arr = resultSetToJson(res);
	        if (arr == null) {
	        	return JsonResult.parse("").setCount(0); 
			}
	        if (arr.length() >= 1) {
	        	int count = 0;
	        	sqlsting = sqlsting.toLowerCase();
	        	if (sqlsting.contains("limit")) {
					sqlsting = sqlsting.split("limit")[0];
					res = stmt.executeQuery(sqlsting);
		    		JSONArray arr1 = resultSetToJson(res);
		    		count = arr1.length();
		    		stmt.close();
				}else {
					count = arr.length();
				}
	    		return JsonResult.parse("{\"data\":"+arr.toString()+"}").setCount(count);
			}else {
				return JsonResult.parse("").setResult("").setCount(0);
			}
		} catch (Exception e) {
			return null;
		}
	}
	
	//查询sql
	public static StringBuffer selects(String sqlsting) throws SQLException, ClassNotFoundException {
		mysqlConnect("admin");
		Statement stmt = conn.createStatement();
		ResultSet res;
		StringBuffer stringBuffer = new StringBuffer();
		res = stmt.executeQuery(sqlsting);  
		stmt.close();
		stringBuffer = resultSetToStringBuffer(res);
		return stringBuffer;
	}

	private static StringBuffer resultSetToStringBuffer(ResultSet rs) throws SQLException,JSONException  
	{  
	   StringBuffer stringBuffer = new StringBuffer();  
	    
	   //获取列数
	   ResultSetMetaData metaData = (ResultSetMetaData) rs.getMetaData();  
	   int columnCount = metaData.getColumnCount();  
	    
	   for (int i = 1; i <= columnCount; i++) {  
           String columnName = metaData.getColumnLabel(i);
           if (stringBuffer.length() == 0) {
        	   stringBuffer.append(columnName);
           }else{
        	   stringBuffer.append(",").append(columnName);
           }
       }
	   
	   //遍历ResultSet中的每条数据  
	   while (rs.next()) {  
		   stringBuffer.append("\n");
		   // 遍历每一列  
	        for (int i = 1; i <= columnCount; i++) {  
	            String columnName = metaData.getColumnLabel(i);  
	            String value = rs.getString(columnName);  
	            if (value == null || "".equals(value)) {
					value = " ";
				}
	            if (i == 1) {
					stringBuffer.append(value);
				}else{
					stringBuffer.append(",").append(value);
				}
	        }
	    }
	    return stringBuffer;
	}
	
	private static JSONArray resultSetToJson(ResultSet rs) {  
	   // json数组  
	   JSONArray array = new JSONArray();  
	   try {
			// 获取列数  
			ResultSetMetaData metaData = (ResultSetMetaData) rs.getMetaData();  
			int columnCount;
			columnCount = metaData.getColumnCount();
			// 遍历ResultSet中的每条数据  
		    while (rs.next()) {  
		        JSONObject jsonObj = new JSONObject();  
		         
		        // 遍历每一列  
		        for (int i = 1; i <= columnCount; i++) {  
		            String columnName =metaData.getColumnLabel(i);  
		            String value = rs.getString(columnName);  
		            if (value == null) {
						value = "";
					}
		            jsonObj.put(columnName, value);  
		        }   
		        array.put(jsonObj);   
		    }  
		} catch (Exception e) {
			e.printStackTrace();
		}  
	    
	    
	    
	   return array;  
	}

	public static String getHost() {
		return host;
	}

	public static void setHost(String host) {
		Mysql.host = host;
	}

	public static String getDbname() {
		return dbname;
	}

	public static void setDbname(String dbname) {
		Mysql.dbname = dbname;
	}

	public static String getUsername() {
		return username;
	}

	public static void setUsername(String username) {
		Mysql.username = username;
	}

	public static String getPwd() {
		return pwd;
	}

	public static void setPwd(String pwd) {
		Mysql.pwd = pwd;
	} 
	
}
